package com.southwind.dao.impl;

import com.southwind.dao.BuildingDao;
import com.southwind.entity.Building;
import com.southwind.entity.Dormitory;
import com.southwind.util.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BulidingDaoImpl implements BuildingDao {

    @Override
    public List<Building> list() {
        Connection con = JDBCUtil.getConnection();
        PreparedStatement stmt = null;
        ResultSet resultSet = null;
        String sql = "select b.id, b.name, b.introduction, d.name, d.id from building b, dormitory_admin d where b.admin_id = d.id";
        List<Building> list = new ArrayList<>();
        try {
            stmt = con.prepareStatement(sql);
            resultSet = stmt.executeQuery();
            while(resultSet.next())
            {
                int id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                String introduction = resultSet.getString(3);
                String adminName = resultSet.getString(4);
                Integer adminId = resultSet.getInt(5);
                list.add(new Building(id, name, introduction, adminId, adminName));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtil.release(con,stmt,resultSet);
        }

        return list;
    }

    @Override
    public List<Building> search(String key, String value) {
        Connection con = JDBCUtil.getConnection();
        PreparedStatement stmt = null;
        ResultSet resultSet = null;
        String sql = "select b.id, b.name, b.introduction, d.name, d.id from building b, dormitory_admin d where b.admin_id = d.id and b."+key+" like '%"+value+"%'";
        List<Building> list = new ArrayList<>();
        try {
            stmt = con.prepareStatement(sql);
            resultSet = stmt.executeQuery();
            while(resultSet.next())
            {
                int id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                String introduction = resultSet.getString(3);
                String adminName = resultSet.getString(4);
                Integer adminId = resultSet.getInt(5);
                list.add(new Building(id, name, introduction, adminId, adminName));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtil.release(con,stmt,resultSet);
        }

        return list;
    }

    @Override
    public Integer save(Building building) {
        Connection con = JDBCUtil.getConnection();
        PreparedStatement stmt = null;
        Integer result = null;
        String sql = "insert into building(name, introduction, admin_id) values(?,?,?)";
        try {
            stmt = con.prepareStatement(sql);
            stmt.setString(1, building.getName());
            stmt.setString(2, building.getIntroduction());
            stmt.setInt(3, building.getAdminId());
            result = stmt.executeUpdate();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtil.release(con,stmt, null);
        }

        return result;
    }

    @Override
    public Integer update(Building building) {
        Connection con = JDBCUtil.getConnection();
        PreparedStatement stmt = null;
        Integer result = null;
        String sql = "update building set name=?,introduction=?,admin_id=? where id=? ";
        try {
            stmt = con.prepareStatement(sql);
            stmt.setString(1, building.getName());
            stmt.setString(2, building.getIntroduction());
            stmt.setInt(3, building.getAdminId());
            stmt.setInt(4, building.getId());
            result = stmt.executeUpdate();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtil.release(con,stmt, null);
        }

        return result;
    }

    @Override
    public Integer delete(Integer id) {
        Connection con = JDBCUtil.getConnection();
        PreparedStatement stmt = null;
        Integer result = null;
        String sql = "delete from building where id = " + id;
        try {
            stmt = con.prepareStatement(sql);
            result = stmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtil.release(con,stmt,null);
        }

        return result;
    }



}
